﻿/*
    日   期:  2013.6.4
    作   者:  张存
    邮   箱:  zhangcunliang@126.com

共享锁S，加锁后，其它事务只能叠加S；独占锁X，加锁后具有排它性，其它事务加不了锁。
① ReadUnCommitted
不加锁，也不接受独占锁，在这个事务隔离级别下可以读到脏数据(未被提交的过期数据或读取后被回滚)，当然也包括不可重复读和幻像数据。
② ReadCommitted
读前加共享锁，读完就释放，可以避免脏读，但是在事务结束之前可能被更改数据，从而导致不可重复的读取(因为被修改，情景不能再现)或幻像数据(被插入或删除)。
③ RepeatableRead
读前加共享锁，事务完成才释放，可重复再现读取，仍会有幻像行产生。
④ Serializable
独占锁，可以保证所有情况都不会发生。

    修改记录:
        2015.1.4    修改并发时共用一个对象操作数据库引发的连接关闭bug,加锁处理
        2015.1.23   所有公共方法该为虚方法,允许子类去重写实现(为其它非标准ado访问用)
        2016.8.30   当执行数据库异常时,OnDbExceptionHandle 处理
        2016.4.26   引入Oracle.ManageddataAccess 后Dispose再次使用（open）会引发异常
        2020.2.25   去掉DBReader 读取时的等待的方法（忘记为什么加了，但是事务读取时会长时间等待关闭）
        2020.3.8    开启事务增加事务级别 IsolationLevel 
        2020.11.12  增加批量插入的实现 仅支持sqlserver与mysql
        2020.2.10   当事务操作时，事务未开始状态引发异常后的回滚发生异常的处理（事务对象为空）
 */
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Threading;
using System.Collections;
using System.Linq;

namespace ASmile.ORM.ADO
{
    /// <summary>
    /// 数据库访问基类（ADO.Net 封装）
    /// </summary>
    public abstract class IDBHelper
    {
        /// <summary>
        /// 直接获得连接字符串
        /// </summary>
        /// <param name="connStr"></param>
        public IDBHelper(string connStr)
        {
            ConnStr = connStr;
            _LockExecNonQuery = new object();
            _LockGetDataReader = new object();
            _LockGetScalar = new object();
            _LockGetDataSet = new object();
            //_ExecReaderWait = new AutoResetEvent(true);
        }
        /// <summary>
        /// 连接字符串
        /// </summary>
        public string ConnStr { get; private set; }
        /// <summary>
        /// 是否事务
        /// </summary>
        protected bool IsTrans { get; private set; } = false;

        readonly object _LockExecNonQuery;
        readonly object _LockGetDataReader;
        readonly object _LockGetScalar;
        readonly object _LockGetDataSet;
        /// <summary>
        /// 数据参数符号，如：SqlServer = "@"
        /// </summary>
        public virtual string ParamSign { get; } = "@";

        /// <summary>
        /// 返回数据库连接
        /// </summary>
        public abstract DbConnection DBConnectionObj { get; }
        /// <summary>
        /// 返回DbCommand对象
        /// </summary>
        public abstract DbCommand DbCommandObj { get; }
        /// <summary>
        /// DbAdapter 对象
        /// </summary>
        protected internal abstract DbDataAdapter DbDataAdapterObj { get; }
        /// <summary>
        /// 事务对象
        /// </summary>
        protected internal DbTransaction DbTransObj { get; private set; }
        ///// <summary>
        ///// 当前连接
        ///// </summary>
        //public DbConnection CurrentConnection
        //{
        //    get
        //    {
        //        return DBConnectionObj;
        //    }
        //}
        /// <summary>
        /// 打开连接,如果已经打开则什么都不执行了
        /// </summary>
        protected virtual void OpenConnection()
        {
            if (DBConnectionObj.State != ConnectionState.Open)
            {
                DBConnectionObj.ConnectionString = ConnStr;
                DBConnectionObj.Open();
            }
        }
        /// <summary>
        /// 关闭连接,如果没有开始事务或连接打开时才关闭
        /// 如果管理了连接返回true
        /// </summary>
        protected virtual bool CloseConnect()
        {
            if (!IsTrans)
            {
                if (DBConnectionObj.State == ConnectionState.Open)
                {
                    DBConnectionObj.Close();
                    //2017.4.26 引入Oracle.ManageddataAccess 后Dispose再次使用（open）会引发异常
                    //DBConnectionObj.Dispose();  
                    return true;
                }
            }
            return false;
        }
        /// <summary>
        /// 给当前DbCommand对象赋值,并且OpenConnection();
        /// </summary>
        void SetCommandAndOpenConnect(string sqlText, CommandType cmdType, params DbParameter[] param)
        {
            //按说赋值Connection,CommandType,是不用多次赋值的
            DbCommandObj.CommandType = cmdType;
            DbCommandObj.Connection = DBConnectionObj;
            DbCommandObj.Parameters.Clear();
            if (param != null)
            {
                DbCommandObj.Parameters.AddRange(param);
            }
            if (string.IsNullOrWhiteSpace(sqlText))
            {
                throw new DBHelperException("sql text is empty", this);
            }
            DbCommandObj.CommandText = sqlText;
            OpenConnection();
        }
        /// <summary>
        /// 将字典键值对转为DbParameter
        /// </summary>
        public DbParameter[] ConvertDbParameter(Dictionary<string, object> paramObj)
        {
            if (paramObj == null) return null;
            List<DbParameter> paramList = new List<DbParameter>();
            foreach (var paramName in paramObj.Keys)
            {
                DbParameter dbParam;
                if (paramName.Substring(0, 1) != ParamSign)
                {
                    dbParam = CreateDbParameter($"{ParamSign}{paramName}", paramObj[paramName]);
                }
                else
                {
                    dbParam = CreateDbParameter(paramName, paramObj[paramName]);
                }
                paramList.Add(dbParam);
            }
            return paramList.ToArray();
        }
        /// <summary>
        /// 创建参数对象
        /// </summary>
        public abstract DbParameter CreateDbParameter(string paramName, object paramValue);
        /// <summary>
        /// 开始执行事务
        /// </summary>
        public virtual void TransStart()
        {
            OpenConnection();
            DbTransObj = DBConnectionObj.BeginTransaction();
            DbCommandObj.Transaction = DbTransObj;
            IsTrans = true;
        }
        /// <summary>
        /// 开始执行事务，并指定事务级别
        /// </summary>
        public virtual void TransStart(IsolationLevel level)
        {
            OpenConnection();
            DbTransObj = DBConnectionObj.BeginTransaction(level);
            DbCommandObj.Transaction = DbTransObj;
            IsTrans = true;
        }
        /// <summary>
        /// 事务提交
        /// </summary>
        public virtual void TransCommit()
        {
            IsTrans = false;
            DbTransObj.Commit();
            CloseConnect();
        }
        /// <summary>
        /// 事务回滚
        /// </summary>
        public virtual void TransRollback()
        {
            IsTrans = false;
            DbTransObj?.Rollback();
            CloseConnect();
        }

        public virtual void Test()
        {
            try
            {
                OpenConnection();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseConnect();
            }
        }

        /// <summary>
        /// 批量拷贝插入
        /// </summary>
        public abstract int BulkInsert(DataTable data);
        /// <summary>
        /// 批量拷贝插入List 或数组
        /// </summary>
        public virtual int BulkInsert<T>(string tableName, string[] columns, List<T> dataList) where T : class
        {
            //BulkCopy 必须加入所有列
            DataTable dt = new DataTable(tableName);
            foreach (var colName in columns)
            {
                var dataCol = new DataColumn(colName);
                dt.Columns.Add(dataCol);
            }
            foreach (var item in dataList)
            {
                DataRow dr = dt.NewRow();
                var rowData = Reflection.GetPropertyNameAndValue(item);
                foreach (var col in columns)
                {
                    dr[col] = rowData[col] ?? DBNull.Value;
                }
                dt.Rows.Add(dr);
            }
            return BulkInsert(dt);
        }
        /// <summary>
        /// 执行一条指定命令类型(SQL语句或存储过程等)的SQL语句,返回所影响行数
        /// </summary>
        public virtual int ExecNonQuery(string sqlText, CommandType cmdType, params DbParameter[] param)
        {
            lock (_LockExecNonQuery)
            {
                try
                {
                    SetCommandAndOpenConnect(sqlText, cmdType, param);
                    return DbCommandObj.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw new DBHelperException("执行 ExecNonQuery 异常", this, ex);
                }
                finally
                {
                    CloseConnect();
                }
            }
        }
        /// <summary>
        /// 执行一条普通SQL语句的命令,返回所影响行数
        /// </summary>
        public virtual int ExecNonQuery(string sqlText, params DbParameter[] param)
        {
            return ExecNonQuery(sqlText, CommandType.Text, param);
        }
        /// <summary>        
        /// 获得DataReader对象
        /// </summary>
        public virtual DbDataReader GetDataReader(string sqlText, CommandType cmdType, CommandBehavior cmdBehavior, params DbParameter[] param)
        {
            lock (_LockGetDataReader)
            {
                try
                {
                    //TODO: 这里忘记为什么加等待了，可能是并发有关
                    //if (DBConnectionObj.State == ConnectionState.Open)
                    //{
                    //    //等待，是否关闭
                    //    DBConnectionObj.StateChange += DBConnectionObj_StateChange;
                    //    _ExecReaderWait.WaitOne();
                    //}
                    SetCommandAndOpenConnect(sqlText, cmdType, param);
                    DbDataReader dbReader = DbCommandObj.ExecuteReader(cmdBehavior);
                    return dbReader;
                }
                catch (Exception ex)
                {
                    throw new DBHelperException("执行 GetDataReader 异常", this, ex);
                }
                finally
                {
                    //DataReader用dbReader对象来关闭,即使非事务也是,不要把注释取消
                    //CloseConnect();
                }
            }
        }
        /// <summary>
        /// 获得DataReader对象
        /// </summary>
        public virtual DbDataReader GetDataReader(string sqlText, CommandType cmdType, params DbParameter[] param)
        {
            CommandBehavior cmdBehavior;
            if (IsTrans)
            {
                cmdBehavior = CommandBehavior.Default;
            }
            else
            {
                //非事务时,关闭DataReader则关闭当前连接
                cmdBehavior = CommandBehavior.CloseConnection;
            }
            return GetDataReader(sqlText, cmdType, cmdBehavior, param);
        }
        /// <summary>
        /// 执行sql语句返回DataReader对象
        /// </summary>
        public virtual DbDataReader GetDataReader(string sqlText, params DbParameter[] param)
        {
            return GetDataReader(sqlText, CommandType.Text, param);
        }
        /// <summary>
        /// 获得首行首列
        /// </summary>
        public virtual object GetScalar(string sqlText, CommandType cmdType, params DbParameter[] param)
        {
            lock (_LockGetScalar)
            {
                try
                {
                    SetCommandAndOpenConnect(sqlText, cmdType, param);
                    return DbCommandObj.ExecuteScalar();

                }
                catch (Exception ex)
                {
                    throw new DBHelperException("执行 GetScalar 异常", this, ex);
                }
                finally
                {
                    CloseConnect();
                }
            }
        }
        /// <summary>
        /// 执行SQL语句,返回首行首列
        /// </summary>
        public virtual object GetScalar(string sqlText, params DbParameter[] param)
        {
            return GetScalar(sqlText, CommandType.Text, param);
        }
        /// <summary>
        /// 执行一条SQL语句返回DataSet对象
        /// </summary>
        public virtual DataSet GetDataSet(string sqlText, CommandType cmdType, params DbParameter[] param)
        {
            lock (_LockGetDataSet)
            {
                try
                {
                    SetCommandAndOpenConnect(sqlText, cmdType, param);
                    DbDataAdapterObj.SelectCommand = DbCommandObj;
                    DataSet ds = new DataSet();
                    DbDataAdapterObj.Fill(ds);
                    return ds;
                }
                catch (Exception ex)
                {
                    throw new DBHelperException("执行 GetDataSet 异常", this, ex);
                }
                finally
                {
                    CloseConnect();
                }
            }
        }
        /// <summary>
        /// 执行一条SQL语句返回DataSet对象
        /// </summary>        
        public virtual DataSet GetDataSet(string sqlText, params DbParameter[] param)
        {
            return GetDataSet(sqlText, CommandType.Text, param);
        }
        /// <summary>
        /// 执行一条SQL语句返回DataTable对象(调用GetDataSet)
        /// </summary>        
        public virtual DataTable GetDataTable(string sqlText, params DbParameter[] param)
        {
            return GetDataTable(sqlText, CommandType.Text, param);
        }
        /// <summary>
        /// 执行一条SQL语句返回DataTable对象(调用GetDataSet)
        /// </summary>
        public virtual DataTable GetDataTable(string sqlText, CommandType cmdType, params DbParameter[] param)
        {
            return GetDataSet(sqlText, cmdType, param).Tables[0];
        }
    }
}